BEGIN TRAN

EXEC msdb.dbo.sp_send_dbmail

@recipients=N'ychoi@farmerbros.com',
@body='The query result is attached', 

@subject ='Distribution Result',
@profile_name ='YCHOI',
@query ='Use Distribution
select a.article, c.article_id,count(c.article_id) as [number of changes to be Processed]
from MSrepl_commands c, MSarticles a
where c.article_id = a.article_id
group by a.article, c.article_id
order by [number of changes to be Processed] DESC;', 
@attach_query_result_as_file = 1,
@query_attachment_filename ='Results.txt'


ROLLBACK
kill 100


USE msdb ;
GO

-- Show the subject, the time that the mail item row was last
-- modified, and the log information.
-- Join sysmail_faileditems to sysmail_event_log 
-- on the mailitem_id column.
-- In the WHERE clause list items where danw was in the recipients,
-- copy_recipients, or blind_copy_recipients.
-- These are the items that would have been sent
-- to danw.

SELECT items.subject,
    items.last_mod_date
    ,l.description FROM dbo.sysmail_faileditems as items
INNER JOIN dbo.sysmail_event_log AS l
    ON items.mailitem_id = l.mailitem_id
WHERE items.recipients LIKE '%ychoi%'  
    OR items.copy_recipients LIKE '%ychoi%' 
    OR items.blind_copy_recipients LIKE '%ychoi%'
GO
